use [efy-mtl-v1]
--------------------------------------------------------List Proceduces------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mTL_SearchTrainingGetSingle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mTL_SearchTrainingGetSingle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mTL_CommentGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mTL_CommentGetAll]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mTL_SearchLearningGetSingle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mTL_SearchLearningGetSingle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mTL_CommentUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mTL_CommentUpdate]
GO
----------------------------------------------------------List Function--------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getSubChildNode]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getSubChildNode]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getSubParentNode]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getSubParentNode]
GO
----------------------------------------------------------------------------------------------------------------------------------------------------
/**
SP mTL_SearchTrainingGetAll
Author: TuanPM
Since: 09/01/2013
**/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].mTL_SearchTrainingGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].mTL_SearchTrainingGetAll
GO
CREATE PROCEDURE dbo.mTL_SearchTrainingGetAll
	@td03									Varchar(50)			--pk_tree
	,@code									Varchar(50)
	,@author								Varchar(50)
	,@TD18									Varchar(2000)		--tieu chi tim kiem	
	,@type									Nvarchar(20)		-- @type = ALL: tim kiem toan bo du lieu, khong quan tam toi dieu kien ben tree
	,@TR06									Varchar(20)			-- Loai cay du lieu
	,@TR09									Varchar(50)			-- don vi trien khai
	,@TD21									Varchar(50)			-- Id can bo co quyen xem
	,@sstaff_permisstion					Varchar(30)			-- Quyen cua NSD
	,@link_db_user							Varchar(50)
	,@currentPage				Int					-- Trang hiện thời
	,@numberRecordPerPage		Int					-- Số bản ghi trên trang	
WITH ENCRYPTION
AS
	Declare @sSql nvarchar(4000)
			,@iTotalRecord int
			,@treeCode Varchar(50)
			,@treeName varchar(2000)
			,@pathNode varchar(4000)			
	SET NOCOUNT ON	
		if @td03 is not null And @td03 <> ''
			Begin
				Select @treeCode = C_INTERNAL_ORDER From T_mTL_TREE Where PK_TREE = @td03
				Create Table #T_03 (TD03 Varchar(50) ,TR04 Nvarchar(200))
				Insert Into #T_03(TD03, TR04)
				Select PK_TREE, dbo.getSubParentNode(PK_TREE,'') + ',' + C_NAME
				From T_mTL_TREE 
				Where	(C_INTERNAL_ORDER like '%'+@treeCode+'%'
						Or PK_TREE = @td03) AND C_TYPE = @TR06	
--				Select @pathNode = dbo.getSubParentNode(convert(varchar(50),@td03),'') 				
			End		
		-- Tao bang tam chua toan bo cac ban ghi thoa man dieu kien dau vao
		Create Table #T_ALL_TRAINING_DOC(	P_ID int IDENTITY (1,1)
											,PK_TRAINING_DOC	uniqueidentifier
											,C_NAME varchar(4000)			
										)	
		Set @sSql = ' Insert into #T_ALL_TRAINING_DOC(PK_TRAINING_DOC, C_NAME)'				
		Set @sSql = @sSql + ' Select PK_TRAINING_DOC, dbo.getSubParentNode(A.FK_TREE,'''')'
		Set @sSql = @sSql + ' From T_mTL_TRAINING_DOC A Inner join T_mTL_TREE B on A.FK_TREE = B.PK_TREE'	
		Set @sSql = @sSql + ' inner join ' + @link_db_user + '.dbo.T_USER_STAFF C on C.PK_STAFF = A.FK_CREATER '	
		Set @sSql = @sSql + ' Where 1 = 1'
		if(@type = 'All')
			Begin
				if @TD18 is not null and @TD18 <> ''
					Begin
						Set @TD18 = dbo.Lower2Upper(@TD18)
						Set @TD18 = '%'+@TD18+'%'
						Set @sSql = @sSql + ' And (A.C_DATA_SEARCH like '+ char(39) +@TD18 + char(39) +')'
					End
			End
		else 
			Begin
				If @td03 Is Not Null And @td03 <> ''			
					Begin
						Set @sSql = @sSql + ' And A.FK_TREE In (Select TD03 From #T_03)'
					End
				if @TD18 is not null and @TD18 <> ''
					Begin
						Set @TD18 = dbo.Lower2Upper(@TD18)
						Set @TD18 = '%'+@TD18+'%'
						Set @sSql = @sSql + ' And A.C_DATA_SEARCH like '+ char(39) + @TD18+ char(39)
					End
			End
		If @TR06 Is Not Null And @TR06 <> ''			
			Set @sSql = @sSql + ' And B.C_TYPE = ' + char(39) + @TR06 +	char(39)
		If @sstaff_permisstion <> 'ADMIN_SYSTEM'
			Begin				
				If @TR09 Is Not Null And @TR09 <> ''
					Begin
						Set @sSql = @sSql + ' And A.C_OWNER_CODE = ' + char(39) + @TR09 + char(39)
					End
			End
		If @TD21 is not null and @TD21 <> ''
			Begin
				Set @TD21 = '%' + @TD21 + '%'
				set @sSql = @sSql + ' and A.C_STATUS=''CHUYEN_LUU'' '
				Set @sSql = @sSql + ' and (A.C_VIEW_PERMISSION = ''ALL'' or A.FK_STAFF_LIST like ' +char(39) + @TD21 + char(39)+ ' or C.FK_UNIT=(select FK_UNIT from '+ @link_db_user +'.dbo.T_USER_STAFF where PK_STAFF like '+char(39)+@TD21+char(39)+') ) '
			End
		If @code is not null and @code <> ''
			Begin
				Set @sSql = @sSql + ' And CHARINDEX('+ char(39) + @code + char(39) + ', A.C_CLASS_CODE_LIST) > 0'
			End
		If @author is not null and @author <> ''
			Begin
				Set @sSql = @sSql + ' And A.C_TAC_GIA =' + char(39) + @author + char(39)
			End
		Set @sSql = @sSql + ' ORDER BY C_TRAINING_YEAR_FROM DESC'	
		PRINT @sSql
		EXEC (@sSql)		
		-- Dem tong so ban ghi
		Select @iTotalRecord = count(*)  From #T_ALL_TRAINING_DOC	
		Delete From #T_ALL_TRAINING_DOC
		Where	P_ID <=((@currentPage-1) * @numberRecordPerPage) 
				or P_ID > @currentPage * @numberRecordPerPage	
		
		--Thuc hien lay DL
		Select	A.PK_TRAINING_DOC As TD01
				,(convert(varchar(4),B.C_TRAINING_YEAR_FROM) + ' - ' + convert(varchar(4),B.C_TRAINING_YEAR_TO)) As TD0809				
				,B.C_NAME As TD02
				,replace(B.C_CLASS_NAME_LIST,'!@!','; ') As TD19
				,B.C_CREATER_POSITION_NAME As TD05
				,B.C_STATUS As TD06
				,B.C_FILENAME_LIST As TD23
				,A.C_NAME As TR04
				,C_VIEW_PERMISSION
				,@iTotalRecord As C_TOTAL_RECORD
				--,@pathNode As pathNode
				,B.C_FILENAME_THUMB
			From #T_ALL_TRAINING_DOC A
			Inner Join T_mTL_TRAINING_DOC B On A.PK_TRAINING_DOC = B.PK_TRAINING_DOC
			Order By B.C_CREATE_DATE DESC
		
	SET NOCOUNT OFF
Return 0
Go
/*
Exec mTL_SearchTrainingGetAll '','','All','TRAINING','THCSCHUVANAN','B0C2DDF7-A129-48A4-822D-D8EAC6FD7D01','DBLink.[efy-user-ngoquyen-2012]','1','50'
Exec mTL_SearchTrainingGetAll '','','All','TRAINING','THCSCHUVANAN','5C6E273C-FB9D-4ADB-B4DF-11E8B03A568D','DBLink.[efy-user-ngoquyen-2012]','1','50'
*/
-----------------------------------------------------------------------------------------------------------------------------------------------
/**
SP mTL_SearchTrainingGetSingle
Author: TuanPM
Since: 09/01/2013
**/
Create procedure mTL_SearchTrainingGetSingle
	@TD01 varchar(50)
As
	SET NOCOUNT ON
	Select 
			PK_TRAINING_DOC
			,C_NAME					AS TD02
			,FK_TREE				AS TD03
			,FK_CREATER				AS TD04
			,C_CREATER_POSITION_NAME AS TD05
			,C_STATUS As TD06
			,C_TRAINING_YEAR_FROM	AS TD08
			,C_TRAINING_YEAR_TO		AS TD09
			,dbo.getSubParentNode(FK_TREE,'''') As TR04
			,(convert(varchar(4),C_TRAINING_YEAR_FROM) + ' - ' + convert(varchar(4),C_TRAINING_YEAR_TO)) As TD0809		
			,replace(C_CLASS_NAME_LIST,'!@!','; ') As TD19
			,C_FILENAME_LIST As TD23
			,C_IDEA_LEADER
			,C_TAC_GIA
			,C_GROUP_USER_DOWN
			,C_GROUP_USER_VIEW
			,C_FILE_FLASH
			,C_FILE_DOCUMENT
			,FK_STAFF_DOWN_LIST
			,C_DOWN_PERMISSION
	From T_mTL_TRAINING_DOC 
	Where PK_TRAINING_DOC = convert(varchar(50),@TD01)
	SET NOCOUNT OFF
Go
/*
exec mTL_SearchTrainingGetSingle'{C3FB0735-73C6-44CF-BCCD-85676F1AAEE4}'
*/
-----------------------------------------------------------------------------------------------------------------------------------
/**
SP mTL_CommentGetAll
Author: TuanPM
Since: 09/01/2013
**/
Create procedure mTL_CommentGetAll
	@RC01  varchar(50)
	,@RC02 varchar(50)
	,@StaffId varchar(50)
As
	SET NOCOUNT ON
	if @RC01 is not null and @RC01 <> ''
		Begin
			Select	FK_STAFF As RC03
					,C_POSITION_NAME										AS RC04
					,convert(nvarchar(20),C_COMMENT_DATE,103)				AS RC05
					,C_CONTENT												AS RC06 
					From T_mTL_COMMENT 
					Where PK_COMMENT = @RC01
		End
	Else
		Begin
			Select  PK_COMMENT												AS RC01
					,FK_STAFF												AS RC03
					,C_POSITION_NAME										AS RC04
					,convert(nvarchar(20),C_COMMENT_DATE,103)				AS RC05
					,C_CONTENT												AS RC06
					,dbo.f_CheckPermission(@StaffId,FK_STAFF) As CHECK_PERMISSION
					From T_mTL_COMMENT 
					Where FK_RECORD = @RC02
			order by C_COMMENT_DATE desc		
		End
	SET NOCOUNT OFF
Go
/*
exec mTL_CommentGetAll '','{139E3C88-7ED4-453A-B972-A7B006966D90}'
*/
----------------------------------------------------------------------------------------------------------------------------------------
/**
SP mTL_CommentUpdate
Author: TuanPM
Since: 09/01/2013
**/
Create procedure mTL_CommentUpdate
	@RC01	varchar(50)
	,@RC02	varchar(50)    ----id hồ sơ
	,@RC03	varchar(50)	   ----id người thực hiện
	,@RC04  varchar(150)   ----Chức danh, tên người thực hiện
	,@RC06  nvarchar(4000)  ---- Nội dung ý kiên
	
As
	Declare	@RC05  datetime,@newId nvarchar(50)
	SET NOCOUNT ON
	Set @RC05 = getdate()
	If @RC01 is not null and @RC01 <> ''
			Begin
				--EDIT
				Update 	T_mTL_COMMENT
				Set		FK_RECORD		    = convert(varchar(50),@RC02)
						,FK_STAFF			= @RC03
						,C_POSITION_NAME	= @RC04
						,C_COMMENT_DATE		= @RC05
						,C_CONTENT			= @RC06
				Where	PK_COMMENT = @RC01				
				Set @newId = @RC01
			End	
		Else
			Begin
				Set @newId = NEWID()
				Insert into T_mTL_COMMENT(PK_COMMENT,FK_RECORD,FK_STAFF,C_POSITION_NAME,C_COMMENT_DATE,C_CONTENT) 
				Values (@newId
					,convert(varchar(50),@RC02)
					,@RC03
					,@RC04
					,@RC05
					,@RC06
					)
			End
	select @newId As PK_COMMENT
	SET NOCOUNT OFF
Go
/*
Exec mTL_CommentUpdate 'e4136159-4a1e-482c-a5c9-08c485316639','F68643FC-728A-4A30-8B1E-BB3789A790C1','QTM - Admin','BÃ i giáº£ng cÃ²n nhiá»u chá»— khÃ´ng phÃ¹ há»£p vá»›i chÆ°Æ¡ng trÃ¬nh giáº£ng dáº¡y'
*/
-----------------------------------------------------------------------------------------------------------------------------------------------
/**
Author: TuanPM
Since: 09/01/2013
**/
--- Truyen vao 1 node, Lay tat ca cac node cha cua 1 node
CREATE FUNCTION dbo.getSubParentNode(
	@pk_tree		nvarchar(50)
	,@result		nvarchar(4000)
)
	Returns nvarchar(4000)
	WITH ENCRYPTION
	AS
	BEGIN
		Begin
			Set @result = '';
			WITH tmp(FK_TREE, c_name, alevel)  
			as (  
				Select FK_TREE, c_name, 0 as aLevel  
				From t_mtl_tree  
				Where PK_TREE = @pk_tree
				Union ALL
				Select B.FK_TREE, B.C_NAME, A.aLevel + 1
				From tmp As A, t_mtl_tree As B
				Where A.FK_TREE = B.PK_TREE
			)
			select @result = C_NAME + '/ ' + @result from tmp
			set @result = substring(@result,0,len(@result))
		End
		Return @result	
	END
Go
/*
select dbo.getSubParentNode('d52f15da-a360-4006-8873-024cc4e1338d','')
*/
-----------------------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getSubLinkParentNode]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getSubLinkParentNode]
GO
/**
	Author: tamtd
	Since: 26/11/2013
**/
--- Truyen vao 1 node, Lay tat ca cac node cha kem theo id cua 1 node
CREATE FUNCTION dbo.getSubLinkParentNode(
	@pk_tree		nvarchar(50)
	,@result		nvarchar(4000)
)
	Returns nvarchar(4000)
	WITH ENCRYPTION
	AS
	BEGIN
		Begin
			Set @result = '';
			WITH tmp(FK_TREE, c_name, alevel)  
			as (  
				Select FK_TREE, c_name, 0 as aLevel  
				From t_mtl_tree  
				Where PK_TREE = @pk_tree
				Union ALL
				Select B.FK_TREE, B.C_NAME, A.aLevel + 1
				From tmp As A, t_mtl_tree As B
				Where A.FK_TREE = B.PK_TREE
			)
			select @result = C_NAME + '/ ' + @result from tmp
			set @result = substring(@result,0,len(@result))
		End
		Return @result	
	END
Go
-----------------------------------------------------------------------------------------------------------------------------------------
/**
Author: TuanPM
Since: 09/01/2013
**/
--- Truyen vao 1 node, Lay tat ca cac node con cua 1 node
CREATE FUNCTION dbo.getSubChildNode(
	@pk_tree		nvarchar(50)
	,@result	    nvarchar(4000)
)
	Returns nvarchar(4000)
	WITH ENCRYPTION
	AS
	BEGIN
		Begin
			Set @result = '';
			WITH tmp(PK_TREE, c_name, alevel)  
			as (  
				Select PK_TREE, c_name, 0 as aLevel  
				From t_mtl_tree  
				Where FK_TREE = @pk_tree
				Union ALL
				Select B.PK_TREE, B.C_NAME, A.aLevel + 1
				From tmp As A, t_mtl_tree As B
				Where A.PK_TREE = B.FK_TREE
			)
			select @result = convert(nvarchar(50),PK_TREE) + ',' + @result from tmp
			--set @result = substring(@result,0,len(@result))
		End
		Return @result	
	END
Go
/*
select dbo.getSubChildNode('95b9433f-e0f1-49e4-b499-b506496d9347','')
*/
---------------------------------------------------------------------------------------------------------------------------------------
/**
Author: TuanPM
Since: 09/01/2013
**/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mTL_SearchLearningGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mTL_SearchLearningGetAll]
GO
CREATE PROCEDURE dbo.mTL_SearchLearningGetAll
	@td03									Varchar(50)			--pk_tree
	,@dFromDate								Varchar(20)
	,@dToDate								Varchar(20)
	,@TD18									Varchar(2000)		--tieu chi tim kiem	
	,@type									Nvarchar(20)		-- @type = ALL: tim kiem toan bo du lieu, khong quan tam toi dieu kien ben tree
	,@TR06									Varchar(20)			-- Loai cay du lieu
	,@TR09									Varchar(50)			-- don vi trien khai
	,@TD21									Varchar(50)			-- Id can bo co quyen xem
	,@sstaff_permisstion					Varchar(30)			-- Quyen cua NSD
	,@link_db_user							Varchar(50)
	,@currentPage							Int					-- Trang hiện thời
	,@numberRecordPerPage					Int					-- Số bản ghi trên trang	
WITH ENCRYPTION
AS
	Declare @sSql nvarchar(4000)
			,@iTotalRecord int
			,@treeCode Varchar(50)
			,@treeName varchar(2000)
			,@pathNode varchar(4000)			
	SET NOCOUNT ON	
		if @td03 is not null And @td03 <> ''
			Begin
				Select @treeCode = C_INTERNAL_ORDER From T_mTL_TREE Where PK_TREE = @td03
				Create Table #T_03 (TD03 Varchar(50) ,TR04 Nvarchar(200))
				Insert Into #T_03(TD03, TR04)
				Select PK_TREE, dbo.getSubParentNode(PK_TREE,'') + ',' + C_NAME
				From T_mTL_TREE 
				Where	(C_INTERNAL_ORDER like '%'+@treeCode+'%'
						Or PK_TREE = @td03) AND C_TYPE = @TR06	
--				Select @pathNode = dbo.getSubParentNode(convert(varchar(50),@td03),'') 				
			End		
		-- Tao bang tam chua toan bo cac ban ghi thoa man dieu kien dau vao
		Create Table #T_ALL_LEARNING_DOC(	P_ID int IDENTITY (1,1)
										,PK_LEARNING_DOC	uniqueidentifier
										,C_NAME varchar(4000)			
										)	
		Set @sSql = ' Insert into #T_ALL_LEARNING_DOC(PK_LEARNING_DOC, C_NAME)'				
		Set @sSql = @sSql + ' Select PK_LEARNING_DOC, dbo.getSubParentNode(A.FK_TREE,'''')'
		Set @sSql = @sSql + ' From T_mTL_LEARNING_DOC A Inner join T_mTL_TREE B on A.FK_TREE = B.PK_TREE'
		Set @sSql = @sSql + ' inner join ' + @link_db_user + '.dbo.T_USER_STAFF C on C.PK_STAFF = A.FK_CREATER '			
		Set @sSql = @sSql + ' Where 1 = 1'
		if(@type = 'All')
			Begin
				if @TD18 is not null and @TD18 <> ''
					Begin
						Set @TD18 = dbo.Lower2Upper(@TD18)
						Set @TD18 = '%'+@TD18+'%'
						Set @sSql = @sSql + ' And (A.C_DATA_SEARCH like '+ char(39) +@TD18 + char(39) +')'
					End
			End
		else 
			Begin
				If @td03 Is Not Null And @td03 <> ''			
					Begin
						Set @sSql = @sSql + ' And A.FK_TREE In (Select TD03 From #T_03)'
					End
				if @TD18 is not null and @TD18 <> ''
					Begin
						Set @TD18 = dbo.Lower2Upper(@TD18)
						Set @TD18 = '%'+@TD18+'%'
						Set @sSql = @sSql + ' And A.C_DATA_SEARCH like '+ char(39) + @TD18+ char(39)
					End
			End
		If @TR06 Is Not Null And @TR06 <> ''			
			Set @sSql = @sSql + ' And B.C_TYPE = ' + char(39) + @TR06 +	char(39)
		If @sstaff_permisstion <> 'ADMIN_SYSTEM'
			Begin				
				If @TR09 Is Not Null And @TR09 <> ''
					Begin
						Set @sSql = @sSql + ' And A.C_OWNER_CODE = ' + char(39) + @TR09 + char(39)
					End
			End				
		If @TD21 is not null and @TD21 <> ''
			Begin
				Set @TD21 = '%' + @TD21 + '%'
				set @sSql = @sSql + ' and A.C_STATUS=''CHUYEN_LUU'' '
			End
		If @dFromDate is not null and @dFromDate <> ''
			Begin
				Set @sSql = @sSql + ' and ( A.C_CREATE_DATE > convert(datetime, ''' + @dFromDate + ''' ,103) )'
			End
		If @dToDate is not null and @dToDate <> ''
			Begin
				Set @sSql = @sSql + ' and ( A.C_CREATE_DATE < convert(datetime, ''' + @dToDate + ''' ,103) )'
			End
		Set @sSql = @sSql + ' ORDER BY C_LEARNING_YEAR_FROM desc'	
		PRINT @sSql
		EXEC (@sSql)		
		-- Dem tong so ban ghi
		Select @iTotalRecord = count(*)  From #T_ALL_LEARNING_DOC	
		Delete From #T_ALL_LEARNING_DOC
		Where	P_ID <=((@currentPage-1) * @numberRecordPerPage) 
				or P_ID > @currentPage * @numberRecordPerPage	
		--Thuc hien lay DL
		Select	A.PK_LEARNING_DOC As TD01
				,(convert(varchar(4),B.C_LEARNING_YEAR_FROM) + ' - ' + convert(varchar(4),B.C_LEARNING_YEAR_TO)) As TD0809				
				,B.C_NAME As TD02
				,replace(B.C_CLASS_NAME_LIST,'!@!','; ') As TD19
				,B.C_CREATER_POSITION_NAME As TD05
				,B.C_STATUS As TD06
				,B.C_FILENAME_LIST As TD23
				,A.C_NAME As TR04
				,@iTotalRecord As C_TOTAL_RECORD
--				,@pathNode As pathNode
			From #T_ALL_LEARNING_DOC	A
			Inner Join T_mTL_LEARNING_DOC B On A.PK_LEARNING_DOC = B.PK_LEARNING_DOC
			Order By B.C_CREATE_DATE DESC 
		
	SET NOCOUNT OFF
Return 0
Go
/*
Exec mTL_SearchLearningGetAll '','','All','LEARNING','THCSCHUVANAN','8F906D10-A277-4BD2-B8DA-6D8EBA3D052E','1','15'
*/
----------------------------------------------------------------------------------------------------------------------------------------
/**
SP mTL_SearchLearningGetSingle
Author: TuanPM
Since: 09/01/2013
**/
Create procedure mTL_SearchLearningGetSingle
	@TD01 varchar(50)
As
	SET NOCOUNT ON
	Select C_NAME					AS TD02
			,FK_TREE				AS TD03
			,FK_CREATER				AS TD04
			,C_CREATER_POSITION_NAME AS TD05
			,C_STATUS As TD06
			,C_LEARNING_YEAR_FROM	AS TD08
			,C_LEARNING_YEAR_TO		AS TD09
			,dbo.getSubParentNode(FK_TREE,'''') As TR04
			,(convert(varchar(4),C_LEARNING_YEAR_FROM) + ' - ' + convert(varchar(4),C_LEARNING_YEAR_TO)) As TD0809		
			,replace(C_CLASS_NAME_LIST,'!@!','; ') As TD19
			,C_FILENAME_LIST As TD23
	From T_mTL_LEARNING_DOC 
	Where PK_LEARNING_DOC = convert(varchar(50),@TD01)
	SET NOCOUNT OFF
Go
/*
exec mTL_SearchLearningGetSingle'{C3FB0735-73C6-44CF-BCCD-85676F1AAEE4}'
*/
----------------------------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mTL_CommentDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mTL_CommentDelete]
GO
CREATE PROCEDURE [dbo].mTL_CommentDelete
	@PkComments		Varchar(max)		-- Danh sach id cac y kien can xoa
	,@psDelimitor	Varchar(10)		
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		-- Tao bang trung gian
		If @PkComments is not null And @PkComments <> ''
			Begin
				Create Table #T_TEMP(PK_COMMENT Varchar(50))
				Exec Sp_ListToTable @PkComments, 'PK_COMMENT', '#T_TEMP', @psDelimitor
			End
		Begin
			SET XACT_ABORT ON -- Dat che do tu dong Rollback neu co loi xay ra
			BEGIN TRANSACTION
				Delete T_mTL_COMMENT Where PK_COMMENT In (Select PK_COMMENT From #T_TEMP)
			COMMIT TRANSACTION
		End
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec mTL_CommentDelete '{B605DD74-73E7-4A97-875A-34B31A5D6466}','!~~!'
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].mTL_getSubParentNode') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].mTL_getSubParentNode
GO

Create procedure mTL_getSubParentNode
	@pk_tree	varchar(50)
As
	SET NOCOUNT ON
		if @pk_tree ='' Set @pk_tree = null
		;WITH tmp(PK_TREE,FK_TREE, c_name, alevel)  
		as (  
			Select PK_TREE,FK_TREE, c_name, 0 as aLevel  
			From t_mtl_tree  
			Where PK_TREE = @pk_tree
			Union ALL
			Select B.PK_TREE, B.FK_TREE, B.C_NAME, A.aLevel + 1
			From tmp As A, t_mtl_tree As B
			Where A.FK_TREE = B.PK_TREE
		)
		Select convert(varchar(50),PK_TREE) As PK_TREE, C_NAME From tmp
	SET NOCOUNT OFF
Go

